const connection = require('../../sql');
const { v4: uuidv4 } = require('uuid');
class MenuService {
  // 获取菜单树
  async getMenuTree() {
    const statement = `
      SELECT 
        id, pid, path, name, sort, component, redirect,
        icon, title, is_hidden AS isHidden, has_children AS hasChildren,
        is_link AS isLink, is_full AS isFull, is_affix AS isAffix,
        is_keep_alive AS isKeepAlive, permissions, menu_type_cd AS menuTypeCd,
        use_data_scope AS useDataScope
      FROM sys_menu
      WHERE del_flag = 'F'
      ORDER BY sort ASC, id ASC
    `;
    const [menus] = await connection.execute(statement);

    const menuMap = new Map();
    menus.forEach(menu => {
      menu.meta = {
        icon: menu.icon || '',
        title: menu.title,
        isLink: menu.isLink,
        isHidden: menu.isHidden,
        isFull: menu.isFull,
        isAffix: menu.isAffix,
        isKeepAlive: menu.isKeepAlive,
        useDataScope: menu.useDataScope
      };
      menu.children = [];
      delete menu.icon;
      delete menu.title;
      delete menu.isHidden;
      delete menu.isLink;
      delete menu.isFull;
      delete menu.isAffix;
      delete menu.isKeepAlive;
      delete menu.hasChildren;
      delete menu.useDataScope;

      menuMap.set(menu.id, menu);
    });

    const tree = [];
    menus.forEach(menu => {
      if (menu.pid === '0') {
        tree.push(menu);
      } else {
        const parent = menuMap.get(menu.pid);
        if (parent) {
          parent.children.push(menu);
        }
      }
    });

    return tree;
  }
  // 获取当前用户可见的菜单
  async getUserMenus(userId) {
    // 查询用户有权限的菜单
    const statement = `
      SELECT DISTINCT
        m.id,
        m.pid,
        m.path,
        m.name,
        m.sort,
        m.component,
        m.icon,
        m.title,
        m.redirect AS isLink,
        m.is_hidden AS isHidden,
        m.is_full AS isFull,
        m.is_affix AS isAffix,
        m.is_keep_alive AS isKeepAlive,
        m.use_data_scope AS useDataScope,
        m.permissions,
        m.menu_type_cd AS menuTypeCd
      FROM sys_user_role ur
      INNER JOIN sys_role r ON ur.role_id = r.id
      INNER JOIN sys_role_menu rm ON r.id = rm.role_id
      INNER JOIN sys_menu m ON rm.menu_id = m.id
      WHERE ur.user_id = ?
        AND r.del_flag = 'F'
        AND m.del_flag = 'F'
        AND m.menu_type_cd IN ('1002001', '1002002')
      ORDER BY m.sort
    `;
    let results;
    try {
      [results] = await connection.execute(statement, [userId]);
    } catch (error) {
      throw new Error(`数据库查询失败: ${error.message}`);
    }

    // 构建树形结构
    const menuMap = {};
    const rootMenus = [];

    // 将所有菜单放入 map
    results.forEach(menu => {
      menuMap[menu.id] = {
        id: menu.id,
        pid: menu.pid,
        path: menu.path,
        name: menu.name,
        sort: menu.sort,
        component: menu.component,
        meta: {
          icon: menu.icon,
          title: menu.title,
          isLink: menu.isLink || '',
          isHidden: menu.isHidden,
          isFull: menu.isFull,
          isAffix: menu.isAffix,
          isKeepAlive: menu.isKeepAlive,
          useDataScope: menu.useDataScope
        },
        children: [],
        permissions: menu.permissions || '',
        menuTypeCd: menu.menuTypeCd
      };
    });

    // 组织树形结构
    Object.values(menuMap).forEach(menu => {
      if (menu.pid === '0') {
        rootMenus.push(menu);
      } else if (menuMap[menu.pid]) {
        menuMap[menu.pid].children.push(menu);
      }
    });

    // 按 sort 排序子菜单
    const sortChildren = menus => {
      menus.sort((a, b) => a.sort - b.sort);
      menus.forEach(menu => {
        if (menu.children.length > 0) {
          sortChildren(menu.children);
        }
      });
    };
    sortChildren(rootMenus);

    return rootMenus;
  }

  //上级菜单树
  async getMenuTreeForParent(nodeId) {
    const statement = `
      SELECT 
        id, pid, sort, title, menu_type_cd AS menuTypeCd, 
        permissions, name
      FROM sys_menu
      WHERE del_flag = 'F'
      AND menu_type_cd IN ('1002001', '1002002')
      ORDER BY sort ASC, id ASC
    `;
    const [menus] = await connection.execute(statement);

    let excludeIds = new Set();
    if (nodeId) {
      const allMenusStatement = `
        SELECT id, pid
        FROM sys_menu
        WHERE del_flag = 'F'
      `;
      const [allMenus] = await connection.execute(allMenusStatement);

      excludeIds.add(nodeId);
      const findChildren = parentId => {
        allMenus.forEach(menu => {
          if (menu.pid === parentId) {
            excludeIds.add(menu.id);
            findChildren(menu.id);
          }
        });
      };
      findChildren(nodeId);
    }

    const root = {
      id: '0',
      pid: '-1',
      deep: '',
      sort: '',
      title: '根目录',
      menuTypeCd: '',
      permissions: '',
      name: '',
      children: []
    };

    const menuMap = new Map();
    menuMap.set('0', root);
    menus.forEach(menu => {
      if (!excludeIds.has(menu.id)) {
        menu.children = [];
        menuMap.set(menu.id, menu);
      }
    });

    // 构建树结构
    menus.forEach(menu => {
      if (!excludeIds.has(menu.id)) {
        const parent = menuMap.get(menu.pid);
        if (parent) {
          parent.children.push(menu);
        }
      }
    });

    // 从根节点递归计算 deep
    const calculateDeep = (node, currentDeep) => {
      node.deep = currentDeep;
      node.children.forEach(child => {
        calculateDeep(child, currentDeep === '' ? 1 : currentDeep + 1);
      });
    };
    calculateDeep(root, '');

    return [root];
  }

  // 获取单个菜单信息
  async getMenuById(id) {
    // 查询单个菜单详细信息
    const statement = `
      SELECT 
        id, pid, path, name, title, icon, component, redirect, sort,
        menu_type_cd AS menuTypeCd, permissions, is_hidden AS isHidden,
        has_children AS hasChildren, is_link AS isLink, is_full AS isFull,
        is_affix AS isAffix, is_keep_alive AS isKeepAlive, create_time AS createTime,
        update_time AS updateTime, create_id AS createId, update_id AS updateId,
        del_flag AS delFlag, use_data_scope AS useDataScope, delete_id AS deleteId,
        delete_time AS deleteTime
      FROM sys_menu
      WHERE id = ? AND del_flag = 'F'
    `;
    const [result] = await connection.execute(statement, [id]);

    if (!result.length) {
      return null; // 未找到记录
    }

    const menu = result[0];

    // 计算 deep 值
    if (menu.pid === '0') {
      menu.deep = 1; // 第一级菜单
    } else {
      // 查询父节点路径以计算 deep
      const depthStatement = `
        WITH RECURSIVE menu_path AS (
          SELECT id, pid, 1 AS depth
          FROM sys_menu
          WHERE id = ?
          UNION ALL
          SELECT m.id, m.pid, mp.depth + 1
          FROM sys_menu m
          INNER JOIN menu_path mp ON m.id = mp.pid
          WHERE m.del_flag = 'F'
        )
        SELECT MAX(depth) AS deep
        FROM menu_path
        WHERE pid = '0'
      `;
      const [depthResult] = await connection.execute(depthStatement, [id]);
      menu.deep = depthResult[0].deep || 1; // 如果未找到顶级父节点，默认第一级
    }

    // 确保 deleteId 和 deleteTime 为空字符串（如果数据库返回 null）
    menu.deleteId = menu.deleteId || '';
    menu.deleteTime = menu.deleteTime || '';

    return menu;
  }

  // 获取所有按钮权限
  async getButtonPermissions() {
    const statement = `
		  SELECT DISTINCT permissions
		  FROM sys_menu
		  WHERE del_flag = 'F'
		  AND menu_type_cd = '1002003'
		  AND permissions IS NOT NULL
		  AND permissions != ''
		  ORDER BY permissions ASC
		`;
    const [result] = await connection.execute(statement);
    return result.map(row => row.permissions);
  }
  // 验证菜单权限是否存在
  async checkPermissionExists(permissions, id) {
    // 构建查询条件
    let sql = `
		  SELECT COUNT(*) as count
		  FROM sys_menu
		  WHERE permissions = ? AND del_flag = 'F'
		`;
    const params = [permissions];

    // 如果传入 id，排除自身
    if (id) {
      sql += ' AND id != ?';
      params.push(id);
    }

    const [result] = await connection.execute(sql, params);
    return result[0].count;
  }

  // 获取当前用户的角色
  async getUserRoles(userId) {
    // 查询用户的角色权限标识
    const statement = `
		SELECT DISTINCT r.permissions
		FROM sys_user_role ur
		INNER JOIN sys_role r ON ur.role_id = r.id
		WHERE ur.user_id = ?
		AND r.del_flag = 'F'
		AND r.permissions != ''
	  `;
    let result;
    try {
      [result] = await connection.execute(statement, [userId]);
    } catch (error) {
      throw new Error(`数据库查询失败: ${error.message}`);
    }
    // 返回 permissions 数组，空结果返回空数组
    return result.length > 0 ? result.map(row => row.permissions) : [];
  }

  // 删除菜单
  async deleteMenus(ids, userId) {
    const placeholders = ids.map(() => '?').join(',');

    // 检查是否有子菜单
    const checkChildrenSql = `
		  SELECT COUNT(*) as count
		  FROM sys_menu
		  WHERE pid IN (${placeholders}) AND del_flag = 'F'
		`;
    const [childrenResult] = await connection.execute(checkChildrenSql, ids);
    if (childrenResult[0].count > 0) {
      throw new Error('存在子菜单，无法删除');
    }

    // 使用事务确保数据一致性
    const conn = await connection.getConnection();
    try {
      await conn.beginTransaction();

      // 软删除 sys_menu
      const deleteMenuSql = `
			UPDATE sys_menu
			SET del_flag = 'T', update_id = ?, update_time = ?
			WHERE id IN (${placeholders}) AND del_flag = 'F'
		  `;
      const menuParams = [userId, new Date(), ...ids];
      const [menuResult] = await conn.execute(deleteMenuSql, menuParams);

      // 删除关联的 sys_data_role_menu
      const deleteRoleMenuSql = `
			DELETE FROM sys_data_role_menu
			WHERE menu_id IN (${placeholders})
		  `;
      await conn.execute(deleteRoleMenuSql, ids);

      await conn.commit();
      return menuResult;
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }

  //保存菜单
  async createMenu(params, userId) {
    const {
      pid,
      icon = '',
      sort,
      menuTypeCd,
      isLink = 'F',
      isHidden = 'F',
      isFull = 'F',
      isAffix = 'F',
      isKeepAlive = 'F',
      title,
      name = '',
      path = '',
      permissions = '',
      component = ''
    } = params;

    const conn = await connection.getConnection();
    try {
      await conn.beginTransaction();

      // 生成 UUID + 时间戳
      const timestamp = Date.now();
      const uuid = uuidv4().replace(/-/g, '');
      const menuId = `${uuid}${timestamp}`;

      // 计算 deep
      let deep = 0;
      if (pid !== '0') {
        const parentSql = `
			  SELECT deep
			  FROM sys_menu
			  WHERE id = ? AND del_flag = 'F'
			`;
        const [parentResult] = await conn.execute(parentSql, [pid]);
        if (parentResult.length === 0) {
          throw new Error('父菜单不存在或已被删除');
        }
        deep = parentResult[0].deep + 1;
      }

      // 插入 sys_menu
      const insertMenuSql = `
			INSERT INTO sys_menu (
			  id, pid, icon, sort, menu_type_cd, is_link, is_hidden, is_full, is_affix, is_keep_alive,
			  title, name, path, permissions, component, create_id, create_time, del_flag, deep
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'F', ?)
		  `;
      const menuParams = [
        menuId,
        pid,
        icon,
        sort,
        menuTypeCd,
        isLink,
        isHidden,
        isFull,
        isAffix,
        isKeepAlive,
        title,
        name,
        path,
        permissions,
        component,
        userId,
        new Date(),
        deep
      ];
      await conn.execute(insertMenuSql, menuParams);

      // 更新父菜单的 has_children
      if (pid !== '0') {
        const updateParentSql = `
			  UPDATE sys_menu
			  SET has_children = 'T'
			  WHERE id = ? AND del_flag = 'F'
			`;
        await conn.execute(updateParentSql, [pid]);
      }

      await conn.commit();
      return menuId;
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }

  //是否开启数据权限
  async changeDataRole(menuId, userId) {
    const conn = await connection.getConnection();
    try {
      await conn.beginTransaction();

      // 查询当前状态
      const selectSql = `
			SELECT use_data_scope
			FROM sys_menu
			WHERE id = ? AND del_flag = 'F'
		  `;
      const [selectResult] = await conn.execute(selectSql, [menuId]);
      if (selectResult.length === 0) {
        throw new Error('菜单不存在或已被删除');
      }

      // 切换状态
      const newState = selectResult[0].use_data_scope === 'T' ? 'F' : 'T';
      const updateSql = `
			UPDATE sys_menu
			SET use_data_scope = ?, update_id = ?, update_time = ?
			WHERE id = ? AND del_flag = 'F'
		  `;
      const updateParams = [newState, userId, new Date(), menuId];
      const [updateResult] = await conn.execute(updateSql, updateParams);

      await conn.commit();
      return updateResult;
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }

  async updateMenu({
    id,
    pid,
    path,
    name,
    title,
    icon,
    component,
    redirect,
    sort,
    menuTypeCd,
    permissions,
    isHidden,
    hasChildren,
    isLink,
    isFull,
    isAffix,
    isKeepAlive,
    useDataScope,
    deep,
    updateId
  }) {
    const conn = await connection.getConnection();

    try {
      await conn.beginTransaction();

      // 构造更新 SQL
      const sql = `
			UPDATE sys_menu 
			SET 
			  pid = ?, 
			  path = ?, 
			  name = ?, 
			  title = ?, 
			  icon = ?, 
			  component = ?, 
			  redirect = ?, 
			  sort = ?, 
			  menu_type_cd = ?, 
			  permissions = ?, 
			  is_hidden = ?, 
			  has_children = ?, 
			  is_link = ?, 
			  is_full = ?, 
			  is_affix = ?, 
			  is_keep_alive = ?, 
			  use_data_scope = ?, 
			  deep = ?, 
			  update_id = ?, 
			  update_time = NOW()
			WHERE id = ? AND del_flag = 'F'
		  `;
      const params = [
        pid,
        path,
        name,
        title,
        icon,
        component,
        redirect,
        sort,
        menuTypeCd,
        permissions,
        isHidden,
        hasChildren,
        isLink,
        isFull,
        isAffix,
        isKeepAlive,
        useDataScope,
        deep,
        updateId,
        id
      ];

      const [result] = await conn.execute(sql, params);

      if (result.affectedRows === 0) {
        throw new Error('菜单不存在或已被删除');
      }

      await conn.commit();
      return { id };
    } catch (error) {
      await conn.rollback();
      throw error;
    } finally {
      conn.release();
    }
  }
}

module.exports = new MenuService();
